﻿Namespace Emeklilik.Maas
	Module Functions

		Function DonemSource() As String
			Return "q@select distinct yil*100+ay as kod, ltrim(yil)+'/'+right('00'+ltrim(ay),2) as ad from yaslılık..yasbankacek order by 1 desc"
		End Function

		Function AylikHakSahipleri(ByVal Process As Token) As Recordset
			Return Process.OpenSQL("select * from yaslılık..yasmaasbil where odeyerkod < 900 order by hturu,kararno,kimlik")
		End Function

		Function Baremler(ByVal Process As Token, tarih As Date) As Recordset
			Return Process.OpenSQL("select * from yaslılık..yasbaremtum where tarih <= @tarih order by tarih desc", tarih)
		End Function

		Function Bordro(ByVal Process As Token, ByVal Yil As Integer, ByVal Ay As Integer) As Recordset
			Q("select oy.odeyerkod,oy.acik as odemeyeri,ym.hturu,ym.kararno,rtrim(ym.adi)+' '+rtrim(ym.soyadi) as adi,ym.kimlik,")
			Q("ym.maas-isnull(ym.kesmaas,0) as maas,case when ym.odeyerkod<20 then right('0000000'+ltrim(c.ceknoyeni),7) else ym.bankahno end as bankahno,")
			Q("case when ym.odeyerkod<20 then ym.cekno end as cekno")
			Q("from yaslılık..yasmaas ym left join yaslılık..odemeyeri oy on ym.odeyerkod = oy.odeyerkod")
			Q("left join muhasebe..cek c on ym.yil = c.yil and ym.ay = c.ay and ym.cekno = c.cekno")
			Q("where ym.yil = @yil and ym.ay = @ay order by oy.odeyerkod,ym.hturu,ym.kararno,ym.kimlik")
			Return Process.OpenSQL("*", Yil, Ay)
		End Function

		Function YurtdisiBordro(ByVal Process As Token, ByVal Yil As Integer, ByVal Ay As Integer) As Recordset
			Q("select oy.odeyerkod,oy.acik as odemeyeri,rtrim(ym.adi)+' '+rtrim(ym.soyadi) as adi,ym.kimlik,yd.bankaadi,yd.bsubeadi,ym.bankahno,ym.maas,ym.vergi,ym.netmaas")
			Q("from yaslılık..yasmaas ym left join yaslılık..odemeyeri oy on ym.odeyerkod = oy.odeyerkod")
			Q("inner join yaslılık..yasyurtdisi yd on ym.kimlik = yd.kimlik and ym.odeyerkod = yd.odeyerkod")
			Q("where ym.yil = @yil and ym.ay = @ay order by oy.odeyerkod,kimlik")
			Return Process.OpenSQL("*", Yil, Ay)
		End Function

		Function Bilgi(ByVal Process As Token, ByVal hturu As String, ByVal kararno As Integer, ByVal Kimlik As String) As Recordset
			Q("select hturu, kararno, kimlik, rtrim(adi)+' '+rtrim(soyadi) as adi, derece, basamak, adr as adres, bankahno as hesapno, ymbastar as tarih,")
			Q("ltrim(o.odeyerkod) + ' - ' + o.acik as odemeyeri, katsayi1, katsayi2, yderece, ykatsayi1, ybasamak")
			Q("from yaslılık..yasmaasbil m left join yaslılık..odemeyeri o on m.odeyerkod = o.odeyerkod")
			Q("where hturu = :~hturu and kararno = :kararno and kimlik = :~kimlik")
			Return Process.OpenSQL("*", hturu, kararno, Kimlik)
		End Function

		Function AldigiMaaslar(ByVal Process As Token, ByVal Kimlik As String) As Recordset
			Q("select a.hturu,a.kararno,a.kimlik,b.ymbastar,rtrim(b.adi)+' '+rtrim(b.soyadi) as adisoyadi,")
			Q("convert(datetime,ltrim(min(yil*100+ay)*100+1)) as ilktarih,convert(datetime,ltrim(max(yil*100+ay)*100+1)) as sontarih")
			Q("from yasmaas a inner join yasmaasbil b on a.hturu = b.hturu and a.kararno = b.kararno and a.kimlik = b.kimlik")
			Q("where ay<13 and a.kimlik = :~kimlik group by a.hturu,a.kararno,a.kimlik,b.ymbastar,b.adi,b.soyadi order by 1,2")
			Return Process.OpenSQL("*", Kimlik)
		End Function

		Function PusulaKaydet(ByVal Process As Token, ByVal Kimlik As String) As Integer
			Dim RowID As Integer = Process.OpenSQL("select isnull(max(subesirano),0)+1 from yaspusula")(0)
			Process.Save("yaspusula", "subesirano,verilistarihi,kimlik", RowID, Process.SysDate, Kimlik)
			Return RowID
		End Function

		Function Dokum(ByVal Process As Token, ByVal hturu As String, ByVal kararno As Integer, ByVal Kimlik As String) As Recordset
			Q("select yil, ay, ayeki, oy.acik as odemeyeri, maas, fon, devlet, tmtfark, cekno")
			Q("from yaslılık..yasmaas ym left join yaslılık..odemeyeri oy on ym.odeyerkod = oy.odeyerkod")
			Q("where hturu = :~hturu and kararno = :kararno and kimlik = :~kimlik")
			Q("order by yil desc, ay desc")
			Return Process.OpenSQL("*", hturu, kararno, Kimlik)
		End Function

		Sub Kaydet(ByVal Token As Token, ByVal hturu As String, ByVal kararno As Integer, ByVal kimlik As String,
		  ByVal odeyerkod As Integer, ByVal bankahno As String,
		  ByVal adr As String, ByVal adrsemt As String, ByVal adrsehir As String)
			Dim Rs As Recordset = Emeklilik.MaasBilgisi(Token, hturu, kararno, kimlik)
			Rs.SetValues("odeyerkod,bankahno,adr,adrsemt,adrsehir,bhesnotar", odeyerkod, bankahno, adr, adrsemt, adrsehir, Token.SysDate)
			Rs.Save()
		End Sub

		Function DonemSorgula(ByVal Token As Token, ByVal yil As Integer, ByVal ay As Integer) As Boolean
			Return Token.OpenSQL("select count(*) from yaslılık..yasmaas where yil = @yil and ay = @ay", yil, ay)(0) > 0
		End Function

		Function BankaCekBordrosu(ByVal Token As Token, ByVal yil As Integer, ByVal ay As Integer) As Recordset
			Q("select ybc.odeyerkod,ybc.cekno,ybc.miktar,ode.acik")
			Q("from yaslılık..yasbankacek ybc inner join yaslılık..odemeyeri ode on ybc.odeyerkod = ode.odeyerkod")
			Q("where yil = @yil and ay = @ay order by ybc.odeyerkod")
			Return Token.OpenSQL("*", yil, ay)
		End Function

		Function BankaCekBordrosuBankaBazinda(ByVal Token As Token, ByVal yil As Integer, ByVal ay As Integer) As Recordset
			Q("select ode.bankano, bk.bankaadi, ybc.cekno, ode.odeyerkod, ode.acik as adi, sum(ybc.miktar) as tutar")
			Q("from yaslılık..yasbankacek ybc left join yaslılık..odemeyeri ode on ybc.odeyerkod = ode.odeyerkod left join yaslılık..bankakod bk on ode.bankano = bk.bankano")
			Q("where yil = @yil and ay = @ay and ode.bankano is not null group by bk.bankaadi, ode.bankano, ybc.cekno, ode.odeyerkod, ode.acik order by ode.bankano, ybc.cekno")
			Return Token.OpenSQL("*", yil, ay)
		End Function

		Function BankaCekBordrosuBolgeBazinda(ByVal Token As Token, ByVal yil As Integer, ByVal ay As Integer) As Recordset
			Q("select b.turu,case when b.turu = 'KOOPERATİF' then ode.bolge else b.bankaadi end as bolge, ode.bankano, ybc.cekno, ode.odeyerkod, ode.acik as adi, sum(ybc.miktar) as tutar")
			Q("from yaslılık..yasbankacek ybc inner join yaslılık..odemeyeri ode on ybc.odeyerkod = ode.odeyerkod inner join yaslılık..bankakod b on ode.bankano = b.bankano")
			Q("where yil = @yil and ay = @ay group by b.turu, b.turu,case when b.turu = 'KOOPERATİF' then ode.bolge else b.bankaadi end, ode.bankano, ybc.cekno, ode.odeyerkod, ode.acik")
			Return Token.OpenSQL("*", yil, ay)
		End Function

	End Module
End Namespace
